package merlin.managers;

import java.sql.Connection;

import java.util.*;

import javax.servlet.http.HttpSession;

import merlin.beans.*;
import merlin.dao.*;
import merlin.utils.*;


public class MsalResearchersManager{


	public static MsalResearchersBean login(String uid,String pwd){
		MsalResearchersBean bean = null;
		Connection con=null;
		ConnectionPool pool = null;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			MsalResearchersDAO dao = new MsalResearchersDAO(con);
			bean = dao.login(uid,pwd);
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return bean;
	}

	public static CustomerDetailsBean loginForFreeUpdates(String emailId){
		CustomerDetailsBean bean = null;
		Connection con=null;
		ConnectionPool pool = null;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			MsalResearchersDAO dao = new MsalResearchersDAO(con);
			bean = dao.loginForFreeUpdates(emailId);
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return bean;
	}

	public static MsalResearchersBean selectMsalResearchersByPK(MsalResearchersBean b){
		MsalResearchersBean bean = null;
		Connection con=null;
		ConnectionPool pool = null;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			MsalResearchersDAO dao = new MsalResearchersDAO(con);
			bean = dao.selectMsalResearchersByPK(b);
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return bean;
	}

	public static void storeSelectedCompanies(String emailAddress,String selectedCompanies,int reportId){

		String companyIds = null;
		if(selectedCompanies != null && selectedCompanies.length() > 0){
			// capture only the company ids.
			String separatorPattern = ApplicationResources.getInstance().getProperty("separatorPattern");
			String arrCompanies[] = selectedCompanies.split(separatorPattern);

			StringBuffer strCompaniesToSave = new StringBuffer();
			for(int i=0;i<arrCompanies.length;i+=2){
				strCompaniesToSave.append('\'').append(arrCompanies[i]).append('\'').append(',');
			}
			strCompaniesToSave.deleteCharAt(strCompaniesToSave.length() - 1);
			companyIds = strCompaniesToSave.toString();
		}

		Connection con=null;
		ConnectionPool pool = null;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();
			con.setAutoCommit(false);
			WebsiteCompanyRequestsDAO dao = new WebsiteCompanyRequestsDAO(con);
			dao.addWebsiteCompanyRequests(emailAddress,companyIds,reportId);
			con.commit();
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}
	}

	public static ArrayList getSelectedCompanies(String emailAddress,int reportId){
		ArrayList list = null;

		Connection conWebsite =null;
		ConnectionPool poolWebsite = null;
		Connection conResearcher =null;
		ConnectionPool poolResearcher = null;

		System.out.println(emailAddress);
		System.out.println(reportId);

		try{
			poolWebsite = ConnectionPool.getConnectionPool("website");
			conWebsite = poolWebsite.getConnection();

			poolResearcher = ConnectionPool.getConnectionPool("researcher");
			conResearcher = poolResearcher.getConnection();

			WebsiteCompanyRequestsDAO wcrdao = new WebsiteCompanyRequestsDAO(conWebsite);
			LookupDetailsDAO lddao = new LookupDetailsDAO(conResearcher);

			String strCompanyIDs = wcrdao.selectWebsiteCompanyRequestsByPK(emailAddress,reportId);

			if(strCompanyIDs != null && strCompanyIDs.length() > 0){
				list = lddao.selectCompanyNumberAndNameByPK(strCompanyIDs);
			}else{
				list = new ArrayList();
			}
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			poolWebsite.returnConnection(conWebsite);
			conWebsite=null;
			poolResearcher.returnConnection(conResearcher);
			conResearcher=null;
		}
		return list;
	}

	public static HashMap getAllConfigurationDetails(){
		HashMap configs = null;
		Connection con=null;
		ConnectionPool pool = null;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			SystemConfigurationDAO dao = new SystemConfigurationDAO(con);
			configs = dao.selectSystemConfigurationRecords(null,null);
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return configs;
	}

	public static int addRegistrationDetails(int reportTitlesId,String firstName,String surname,String companyName,String postcode,String emailAddress){
		Connection con=null;
		ConnectionPool pool = null;

		int updateCols = -1;
		try{
			pool = ConnectionPool.getConnectionPool("researcher");
			con = pool.getConnection();

			con.setAutoCommit(false);
			RegistrationDetailsDAO rddao = new RegistrationDetailsDAO(con);
			updateCols = rddao.addRegistrationDetails(reportTitlesId,firstName,surname,companyName,postcode,emailAddress);
			con.commit();
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con = null;
			return updateCols;
		}
	}

	public int addIndustryListing(int reportNumber,String url,String notes,boolean associationIndicator,int qualityScore,String dateLastCheckedString){
		Connection con=null;
		ConnectionPool pool = null;

		int updateCols = -1;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			con.setAutoCommit(false);
			IndustrySourceListingDAO isdao = new IndustrySourceListingDAO(con);

			IndustrySourceListingBean bean = new IndustrySourceListingBean(-1,reportNumber,url,notes,associationIndicator,qualityScore,null);
			bean.setDateLastCheckedString(dateLastCheckedString);

			updateCols = isdao.addIndustrySourceListing(bean);
			con.commit();
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con = null;
			return updateCols;
		}
	}

	public int updateIndustryListing(int sourceId,int reportNumber,String url,String notes,boolean associationIndicator,int qualityScore,String dateLastCheckedString){
		Connection con=null;
		ConnectionPool pool = null;

		int updateCols = -1;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			con.setAutoCommit(false);
			IndustrySourceListingDAO isdao = new IndustrySourceListingDAO(con);
			IndustrySourceListingBean bean = new IndustrySourceListingBean(sourceId,reportNumber,url,notes,associationIndicator,qualityScore,null);
			bean.setDateLastCheckedString(dateLastCheckedString);

			updateCols = isdao.updateIndustrySourceListing(bean);
			con.commit();
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con = null;
			return updateCols;
		}
	}

	public List getIndustrySourceListings(int reportId){
		String orderBy = "ISL_URL";
		List list = null;
		Connection con=null;
		ConnectionPool pool = null;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			IndustrySourceListingDAO dao = new IndustrySourceListingDAO(con);
			list = dao.getIndustrySourceListings(reportId,orderBy);
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}

		return list;
	}

	public String emailSourceList(String mailTo,String reportTitle,IndustrySourceListingBean[] lstSources){
		String status = null;
		try{
			StringBuffer buffer = new StringBuffer();
   			buffer.append(reportTitle).append("<br>");
   			for(int i=0;i<lstSources.length;i++){
				IndustrySourceListingBean bean = (IndustrySourceListingBean)lstSources[i];
				buffer.append("<a href='").append(bean.getUrl()).append("'>").append(bean.getUrl()).append("</a>");
				if(bean.getNotes() != null && bean.getNotes().length() > 0){
					buffer.append(" ").append('(').append(bean.getNotes()).append(")<br>");
				}
   			}
   			MailSender.sendMail("info@merlinscottassociates.co.uk",mailTo,"Source List for " + reportTitle,buffer.toString());
			status ="mail sent to " + mailTo;
		}catch(RuntimeException e){
			Throwable cause = (e.getCause() != null ? e.getCause() : e);
			status = cause.getMessage();
		}

		return status;
	}

	public int deleteIndustrySourceListing(int sourceId){
		Connection con=null;
		ConnectionPool pool = null;

		int updateCols = -1;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			con.setAutoCommit(false);
			IndustrySourceListingDAO isdao = new IndustrySourceListingDAO(con);
			updateCols = isdao.deleteIndustrySourceListing(sourceId);
			con.commit();
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con = null;
			return updateCols;
		}
	}

	public CompanyWebsiteDetailsBean getCompanyWebsiteDetails(String registeredNumber){
		CompanyWebsiteDetailsBean bean = null;

		Connection con=null;
		ConnectionPool pool = null;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

            CompanyWebsiteDetailsDAO dao = new CompanyWebsiteDetailsDAO(con);
            bean = dao.getCompanyWebsiteDetails(registeredNumber);
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con = null;
			return bean;
		}
	}

	public int saveCompanyWebsiteDetails(String registeredNumber,String website,String email,boolean unsubscribe,String personalEmail,String prefix,String firstName,String initials,String surname,String position){
		CompanyWebsiteDetailsBean bean = new CompanyWebsiteDetailsBean(registeredNumber,website,email);
		bean.setUnsubscribe(unsubscribe);
		bean.setPersonalEmail(personalEmail);
		bean.setPrefix(prefix);
		bean.setFirstName(firstName);
		bean.setInitials(initials);
		bean.setSurname(surname);
		bean.setPosition(position);

		Connection con=null;
		ConnectionPool pool = null;

		int updateCols = -1;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			con.setAutoCommit(false);
            CompanyWebsiteDetailsDAO dao = new CompanyWebsiteDetailsDAO(con);
			updateCols = dao.saveCompanyWebsiteDetails(bean);
			con.commit();
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con = null;
			return updateCols;
		}
	}

	public List getCompanyWebsiteDetailsByReportId(int reportId){
		String orderBy = "COMPANY_NAME ASC";
		List list = null;
		Connection conWebsite=null, conResearcher = null;
		ConnectionPool poolWebsite = null, poolResearcher = null;
		try{
			poolWebsite = ConnectionPool.getConnectionPool("website");
			conWebsite = poolWebsite.getConnection();

			poolResearcher = ConnectionPool.getConnectionPool("researcher");
			conResearcher = poolResearcher.getConnection();

			CompanyWebsiteDetailsDAO daoResearcher = new CompanyWebsiteDetailsDAO(conResearcher);
			List tmpResults = daoResearcher.getCompanyDetailsByReportId(reportId, orderBy);

            CompanyWebsiteDetailsDAO dao = new CompanyWebsiteDetailsDAO(conWebsite);
			list = dao.getCompanyWebsiteDetailsByReportId(reportId,tmpResults);
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			poolWebsite.returnConnection(conWebsite);
			conWebsite=null;
			poolResearcher.returnConnection(conResearcher);
			conResearcher=null;
		}

		return list;
	}

	/**
	 * Channels a call to the LookupDetailsManager.
	 * This simple indirection is used to reduce the entries in the
	 * dwr.xml file and client-side DWR JS file entries.
	 */
	public LookupDetailsBean selectLookupDetailsByPK(String pk){
		return LookupDetailsManager.selectLookupDetailsByPK(pk);
	}

	public static List getCompaniesInReport(int reportId){
		String orderBy = "COMPANY_NAME ASC";
		List list = null;
		Connection con=null;
		ConnectionPool pool = null;
		try{
			pool = ConnectionPool.getConnectionPool("researcher");
			con = pool.getConnection();

            CompanyWebsiteDetailsDAO dao = new CompanyWebsiteDetailsDAO(con);
			list = dao.getCompanyDetailsByReportId(reportId,orderBy);
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}

		return list;
	}

	/**
	 * A function to refresh the session to prevent the user's session from getting invalidated.
	 */
	public static void stayConnected(javax.servlet.http.HttpSession session){
		System.out.println("session id " + session.getId() + " connected " + new java.util.Date(session.getLastAccessedTime()));
	}

	public static int setNewReportId(javax.servlet.http.HttpSession session,int reportId,String reportTitle){
		try{
			System.out.println(reportId);
			session.setAttribute("reportId",String.valueOf(reportId));
			session.setAttribute("reportTitle",reportTitle);
			return reportId;
		}catch(Exception e){
			e.printStackTrace();
		}
		return -1;
	}


	//////////////////////////// Functions for Free Updates Customize Reports tab /////////////////////

	public static List getFinancialDetailsForCompaniesByReportAndFinancialType(int reportId, int financialTypeId,String lowerRange, String upperRange, String companiesToInclude, int pageNo){
		List list = null;
		Connection conResearcher=null, conWebsite=null;
		ConnectionPool poolResearcher=null, poolWebsite = null;
		try{
			poolResearcher = ConnectionPool.getConnectionPool("researcher");
			conResearcher = poolResearcher.getConnection();

			poolWebsite = ConnectionPool.getConnectionPool("website");
			conWebsite = poolWebsite.getConnection();

            FinancialDetailsDAO dao = new FinancialDetailsDAO(conResearcher);

			if(reportId < 1000000){
				// a normal report.
				list = dao.selectFinancialDetailsRecordsByReportAndFinancialType(reportId,financialTypeId,lowerRange,upperRange,companiesToInclude, pageNo);
			}else{
				// a custom report.
				String companyIds = null;
				if(companiesToInclude == null){
					ReportTitlesDAO rtdao = new ReportTitlesDAO(conWebsite);
					companyIds = rtdao.getCompanyIdsByCustomReportId(reportId);
				}else{
					companyIds = companiesToInclude;
					// no need for pagination, as the user wants to see data for a specific set of companies.
					pageNo = 0;
				}

				list = dao.selectFinancialDetailsRecordsByCompanyIdsAndFinancialType(financialTypeId,lowerRange,upperRange,companyIds, pageNo);

			}

			if(list != null){
				// clean the Company names.
				for(int i=0;i<list.size();i++){
					FinancialDetailsBean bean = (FinancialDetailsBean)list.get(i);
					bean.setCompanyName(clean(bean.getCompanyName()));
				}

				// sort them.
				Collections.sort(list, new Comparator(){
					public int compare(Object a, Object b){
						return ((FinancialDetailsBean)a).getCompanyName().compareTo(((FinancialDetailsBean)b).getCompanyName());
					}
					public boolean equals(Object obj){
						return true;
					}
				});
			}
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			poolResearcher.returnConnection(conResearcher);
			conResearcher = null;
			poolWebsite.returnConnection(conWebsite);
			conWebsite = null;
		}

		return list;
	}

	@SuppressWarnings("unchecked")
	public static ReportTitlesBean addCustomReport(javax.servlet.http.HttpSession session,String reportTitle,int userId,String companyIds, String selectedForReports){
		Connection conWebsite=null, conReport = null, conLookup = null;
		ConnectionPool poolWebsite = null, poolReport = null, poolLookup = null;
		ReportTitlesBean bean = null;
		try{
			poolWebsite = ConnectionPool.getConnectionPool("website");
			conWebsite = poolWebsite.getConnection();

			poolReport = ConnectionPool.getConnectionPool("report");
			conReport = poolReport.getConnection();

			poolLookup = ConnectionPool.getConnectionPool("lookup");
			conLookup = poolLookup.getConnection();

			ReportTitlesDAO rtdao = new ReportTitlesDAO(conWebsite);
			ReportTitlesDAO rtdaoReport = new ReportTitlesDAO(conReport);
			ReportTitlesDAO rtdaoLookup = new ReportTitlesDAO(conLookup);

			conWebsite.setAutoCommit(false);

			bean = rtdao.addCustomReport(MerlinUtilities.capitalizeFully(reportTitle),userId,companyIds, selectedForReports);
			// Update the additional reports column in CUSTOMER_DETAILS table.
			MsalResearchersDAO mrDao = new MsalResearchersDAO(conWebsite);
			String reportIds = mrDao.addCustomReportToCustomerDetails(userId,bean.getReportTitlesId());

			conWebsite.commit();

			//store the info in the REPORT and LOOKUP databases also.
			rtdaoReport.addCustomReportTitle(bean.getReportTitlesId(), bean.getReportTitle(),bean.getReportCode());
            rtdaoLookup.addCustomReportTitle(bean.getReportTitlesId(), bean.getReportTitle(),bean.getReportCode());

			// store the newly created title into the user's session.
			List list = (DbResultSet)session.getAttribute("reportsForUser");
			list.add(bean);
			Collections.sort(list, new Comparator(){
				public int compare(Object a, Object b){
					return ((ReportTitlesBean)a).getReportTitle().compareTo(((ReportTitlesBean)b).getReportTitle());
				}
				public boolean equals(Object obj){
					return true;
				}
			});
			// also update the user session bean with the updated additional report ids.
   			((CustomerDetailsBean)session.getAttribute("userDetails")).setAdditionalReportNumbers(reportIds);
		}catch(Exception e){
			e.printStackTrace();
			try{
				conWebsite.rollback();
			}catch(Exception ee){
			}
			throw new RuntimeException(e);
		}finally{
			poolWebsite.returnConnection(conWebsite);
			conWebsite=null;
			poolReport.returnConnection(conReport);
			conReport=null;
			poolLookup.returnConnection(conLookup);
			conLookup=null;
		}

		return bean;
	}

	/**
	 * Returns the list of reports a customer has subscribed to.
	 */
	public static List getReportsList(javax.servlet.http.HttpSession session){
		DbResultSet reportsForUser = (DbResultSet)session.getAttribute("reportsForUser");
		return reportsForUser;
	}

	public static List deleteCustomReport(HttpSession session, int customReportId){
		
		CustomerDetailsBean customerBean = (CustomerDetailsBean)session.getAttribute("userDetails");
		List list = (DbResultSet)session.getAttribute("reportsForUser");
		
		Connection conWebsite=null;
		ConnectionPool poolWebsite = null;
		try{
			poolWebsite = ConnectionPool.getConnectionPool("website");
			conWebsite = poolWebsite.getConnection();

			MsalResearchersDAO mrDao = new MsalResearchersDAO(conWebsite);
			String reportIds = mrDao.deleteCustomReportFromCustomerDetails(customerBean.getCustomerDetailsId(), customReportId);
			conWebsite.commit();

			
			// update the user's session.
			customerBean.setAdditionalReportNumbers(reportIds);
			Iterator itr = list.iterator();
			while(itr.hasNext()){
				ReportTitlesBean reportBean = (ReportTitlesBean)itr.next();
				if(reportBean.getReportTitlesId() == customReportId){
					itr.remove();
					break;
				}
			}

		}catch(Exception e){
			e.printStackTrace();
			try{
				conWebsite.rollback();
			}catch(Exception ee){
			}
			throw new RuntimeException(e);
		}finally{
			poolWebsite.returnConnection(conWebsite);
			conWebsite=null;
		}
		return list;
	}
	
	public static List renameCustomReport(HttpSession session, int customReportId, String newReportTitle){
		
		List list = (DbResultSet)session.getAttribute("reportsForUser");
		
		Connection conWebsite=null, conReport = null, conLookup = null;
		ConnectionPool poolWebsite = null, poolReport = null, poolLookup = null;

		try{
			poolWebsite = ConnectionPool.getConnectionPool("website");
			conWebsite = poolWebsite.getConnection();

			poolReport = ConnectionPool.getConnectionPool("report");
			conReport = poolReport.getConnection();

			poolLookup = ConnectionPool.getConnectionPool("lookup");
			conLookup = poolLookup.getConnection();

			ReportTitlesDAO rtdaoWebsite = new ReportTitlesDAO(conWebsite);
			ReportTitlesDAO rtdaoReport = new ReportTitlesDAO(conReport);
			ReportTitlesDAO rtdaoLookup = new ReportTitlesDAO(conLookup);

			newReportTitle = MerlinUtilities.capitalizeFully(newReportTitle);
			 
			int updateCountWS = rtdaoWebsite.renameReportTitle(customReportId, newReportTitle);
			int updateReport = rtdaoReport.renameReportTitle(customReportId, newReportTitle);
			int updateCountLookup = rtdaoLookup.renameReportTitle(customReportId, newReportTitle);
			
			// update the user's session, if the report rename operation was successful.
			if(updateCountLookup > 0 || updateCountWS > 0 || updateReport > 0){
				Iterator itr = list.iterator();
				while(itr.hasNext()){
					ReportTitlesBean reportBean = (ReportTitlesBean)itr.next();
					if(reportBean.getReportTitlesId() == customReportId){
						reportBean.setReportTitle(newReportTitle);
						break;
					}
				}
			}

		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			poolWebsite.returnConnection(conWebsite);
			conWebsite=null;
			poolReport.returnConnection(conReport);
			conReport=null;
			poolLookup.returnConnection(conLookup);
			conLookup=null;
		}
		return list;
	}

	
	private static String clean(String input){
		if(input != null){
			input = input.replace('\"',' ');
			input = input.replaceAll("  "," ");
			input = input.trim();

			if(Character.isLowerCase(input.charAt(0))){
				input = Character.toUpperCase(input.charAt(0)) + input.substring(1);
			}
		}
		return input;
	}

}